<?php
/**
 * <p>Class that can be configured and inserted into your application to automatically
 * upgrade the database structure from SQL files created into a folder.</p>
 *
 * <p>
 * Before using this tool please customize the following functions:<br />
 *
 * dbQuery() - Specifies what function to call to execute a database query,
 * you can set this to your own framework database query or to the basic mysql_query
 * function.<br />
 *
 * getDbVer - Get the current version of your database, set this to any function
 * that will consult your configuration and retrieve the current database version. <br />
 * 
 * updateDbVer - Set this to a function that will store the new database version into
 * your configuration. <br />
 * </p>
 */
class AutoDbUp {

  /**
	 * @var string Directory where all the sql scripts are stored.
	 */
  public $sql_path;

  /**
   * @var string Last error message returned by the database.
   */
  public $sql_error_msg;

  /**
   * A commented keyword that if found at the beginning of the sql file, it will
   * skip the update without running the sql but still updating the database version. <br />
   * Each developer should have a different key defined when calling the autoupdater. <br />
   * Example: When developing from my computer I add this line to all the sql files that I already
   * run on my server, so the script will skip them:
   * -- @skip_1987
   * ..now sql commands here that won't be executed on my macine, just on other's.
   * @var string
   */
  public $skip_keyword;

  const ERR_SQL_PATH  = "[AutoDbUp] Path to the sql files must be passed when calling the class, Ej: new AutoDbUp('path/to/sql_files');";
  const ERR_OPEN_DIR  = "[AutoDbUp] Failed to open the dir: %s";
  const ERR_UPGRADE   = "[AutoDbUp] Failed to upgrade the database to version: %s, error message: <br />%s";

  /**
   * Create an instance of the class and specify wich path to use for the sql scripts.
   * @param string $sql_path Directory where all the sql scripts are stored.
   */
  public function __construct($sql_path = '', $skip_keyword = '')
	{
    // Assign path
		if (strlen($sql_path) > 0)
      $this->sql_path = $sql_path;
    else
      die(self::ERR_SQL_PATH);
    // Assign skip keyword if available
    if (strlen($skip_keyword) > 0)
      $this->skip_keyword = $skip_keyword;
	}

  /**
   * Checks the diference between the versions and executes the new sql files.
   */
  public function upgrade() {
    // Get current version of the db.
    $db_ver = $this->getDbVer();
    // Array to store the new sql files that must be executed.
    $new_revs = array();
    // Open dir. where sql files are stored.
    $handler = @opendir($this->sql_path);
    if (!$handler)
      die(sprintf(self::ERR_OPEN_DIR, $this->sql_path));
    // Loop for each file.
    while ($file = readdir($handler)) {
      $file = strtolower($file);
      // Get the version number of the file,
      // is all its name minus the '.sql' extension.
      $num = intval(substr($file,0,-4));
      // If $file isn't this directory or its parent,
      // and the file's version is higher than the current, 
      // add it to the results array.
      if ($file != '.' && $file != '..' && $num > $db_ver) {
        $file = $this->sql_path.$file;
        $new_revs[$num] = $file;
      }
    }
    // Loop all the new sql files found.
    foreach ($new_revs as $num => $file) {
      // Execute the sql file.
      if ($this->parseMysqlDump($file)) {
        // Upgrade went OK. Now update the database version.
        $this->updateDbVer($num);
      } else {
        // Upgrade FAILED.
        die(sprintf(self::ERR_UPGRADE, $num, $this->sql_error_msg));
        break;
      }
    }
    // Close the dir.
    closedir($handler);
  }

  /**
   * Execute command on database.
   * @param string $sql Sql query.
   */
  private function dbQuery($sql) {
    $query = @mysql_query($sql);
    return $query;
  }

  private function getMysqlError() {
    return mysql_error();
  }

  /**
   * Ger the current version of the database.
   * @return integer Current version of the database.
   */
  public function getDbVer() {
    return intval(Configuration::get("DB_VER"));
  }

  /**
   * Increase the database version number.
   * @param integer $ver New version of the database.
   * @return boolean True if all went fine.
   */
  public function updateDbVer($ver) {
    return Configuration::updateValue("DB_VER", intval($ver));
  }

  /**
   * This function parses the SQL file and executes all the changes on the database.
   * @param string $file Full path and filename for the sql file.
   * @return boolean True if the sql file has been executed with no error.
   */
  private function parseMysqlDump($file) {
      $handle = @fopen($file, "r");
      $query = "";
      $this->beginTransaction();
      $failed = false;
      while(!feof($handle)) {
        $sql_line = trim(fgets($handle));
        // Check if sql file skipping is enabled and if this file must be skipped
        if (strlen($this->skip_keyword) > 0 && strpos($sql_line, $this->skip_keyword) !== false) {
          $this->rollBackTransaction();
          return true;
        }
        // Ignore sql comments
        if (trim($sql_line) != "" && strpos($sql_line, "--") === false) {
          // Add lines and execute query.
          $query .= $sql_line;
          if (preg_match("/;[\040]*\$/", $sql_line)) {
              if (!$this->dbQuery($query)) {
                $failed = true;
                $this->sql_error_msg = $this->getMysqlError();
                break;
              }
              $query = "";
          }
        }
      }
      if ($failed) {
        $this->rollBackTransaction();
        return false;
      } else {
        $this->commitTransaction();
        return true;
      }
  }

  /**
   * Begin a sql transaction.
   * @return boolean True if query went fine.
   */
  private function beginTransaction() {
    return $this->dbQuery("BEGIN");
  }

  /**
   * Commit a sql transaction.
   * @return boolean True if query went fine.
   */
  private function commitTransaction() {
    return $this->dbQuery("COMMIT");
  }

  /**
   * Rollback the sql transaction if erros were found.
   * @return boolean True if query went fine.
   */
  private function rollBackTransaction() {
    return $this->dbQuery("ROLLBACK");
  }
}
?>